package core;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import javax.swing.JFrame;
import javax.swing.JTable;
import util.JTableUtil;

/**
 *
 * @author Marcin
 */
public class Doctor {
    private User user;
    private String specialization;
    private String lic_numer;
    private static JFrame parent;
    public Doctor(User user, String spec, String lic_nr,JFrame frame)
    {
        this.user = user;
        this.specialization=spec;
        this.lic_numer=lic_nr;
        this.parent=frame;
    }
    
    public Doctor(User user,JFrame frame){
        this.user=user;
        this.parent=frame;
    }
    
    public int getID()
    {
        return user.getID();
    }
    
    public void addHoliday(Date from,Date to)
    {
                if(from.compareTo(to) > 0) {
           Date temp = to;
           to = from;
           from = temp;
        } 
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "INSERT INTO medycy_zwolnienia VALUES(?,?,?)",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setInt(1, user.getID());
            stmt.setTimestamp(2, new java.sql.Timestamp(from.getTime()));
            stmt.setTimestamp(3, new java.sql.Timestamp(to.getTime()));
            stmt.executeUpdate();
        }
         catch(SQLException ex)
        {
            ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
    }
    
    public void addWizytaDomowa_Uzytkownik(Date from,Date to, User person, Doctor d)
    {
                if(from.compareTo(to) > 0) {
           Date temp = to;
           to = from;
           from = temp;
        } 
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "INSERT INTO terminarz_wizytydomowe VALUES(?,?,?)",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setInt(1, person.getID());
            stmt.setInt(2, d.getID());
            stmt.setTimestamp(3, new java.sql.Timestamp(from.getTime()));
            stmt.executeUpdate();
        }
         catch(SQLException ex)
        {
            ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
    }
    
    public void delWizytaDomowa_Uzytkownik(Date from,Date to, User person, Doctor d)
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "DELETE FROM terminarz_wizytydomowe WHERE id_pacjenta='"+person.getID()+"' AND id_medyka='"+d.getID()+"' AND data='"+new java.sql.Timestamp(from.getTime())+"'",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.executeUpdate();
        }
         catch(SQLException ex)
        {
            ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
    }
    
        public void addSwojLekarz_Uzytkownik(Date from,Date to, User person, Doctor d)
    {
       if(from.compareTo(to) > 0) {
           Date temp = to;
           to = from;
           from = temp;
        } 
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "INSERT INTO terminarz_wizyty_doktorskie VALUES(?,?,?,?,?)",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setInt(1, person.getID());
            stmt.setInt(2, d.getID());
            stmt.setInt(3, 12);
            stmt.setTimestamp(4, new java.sql.Timestamp(from.getTime()));
            stmt.setTimestamp(5, new java.sql.Timestamp(to.getTime()));
            stmt.executeUpdate();
        }
         catch(SQLException ex)
        {
            ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
    }
    
    public void delSwojLekarz_Uzytkownik(Date from,Date to, User person, Doctor d)
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "DELETE FROM terminarz_wizyty_doktorskie WHERE id_pacjenta='"+person.getID()+"' AND id_medyka='"+d.getID()+"' AND data_start='"+new java.sql.Timestamp(from.getTime())+"'",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.executeUpdate();
        }
         catch(SQLException ex)
        {
            ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
    }
    
    public static void delSwojLekarz_Uzytkownik(String from, int person)
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "DELETE FROM terminarz_wizyty_doktorskie WHERE id_pacjenta='"+person+"' AND data_start='"+from+"'",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.executeUpdate();
        }
         catch(SQLException ex)
        {
            System.out.println("delSwojLekarz_Uzytkownik blad");
           // ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
    }
        
    public void addTerm(Date from,Date to,String dayOfWeek)
    {
                if(from.compareTo(to) > 0) {
           Date temp = to;
           to = from;
           from = temp;
        } 
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "INSERT INTO medycy_terminy VALUES(?,?,?,?)",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setInt(1, user.getID());
            Calendar.getInstance().setTime(to);
            stmt.setString(2, dayOfWeek);
            stmt.setTimestamp(3, new java.sql.Timestamp(from.getTime()));
            stmt.setTimestamp(4, new java.sql.Timestamp(to.getTime()));
            stmt.executeUpdate();
        }
         catch(SQLException ex)
        {
            ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
    }
    
    public ResultSet getTerms()
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "SELECT dzien AS dzien , godzina_start AS data_start, godzina_stop AS data_stop FROM"
                    + " medycy_terminy WHERE id_medyka=?",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setInt(1, user.getID());
            return stmt.executeQuery();
        }
        catch(SQLException ex)
        {
            ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
        return null; 
    }
    
    public ResultSet getPacientsTodayHomeVisit()
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    " SELECT T.id_pacjenta AS ID , T.data AS DATA ,U.imie AS IMIE , U.nazwisko AS NAZWISKO, " +
                    " P.pesel AS PESEL,P.nr_ubezpieczenia AS 'NUMER UBEZPIECZENIA' FROM terminarz_wizytydomowe T"
                    + " JOIN pacjenci P ON T.id_pacjenta=P.id_user " +
                    " JOIN users U ON T.id_pacjenta=U.id " 
                    + " WHERE T.id_medyka=? AND T.data=?",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setInt(1, user.getID());
            stmt.setDate(2, new java.sql.Date(new Date().getTime()));
            return stmt.executeQuery();
        }
        catch(SQLException ex)
        {
            ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
        return null;   
    }
    
    public ResultSet getPacientsVisit(Date from,Date to)
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "SELECT id_pacjenta AS id_pacjenta , id_medyka AS id_medyka , id_gabinetu AS id_gabinetu, "
                    + " data_start AS data_start,data_stop AS data_stop FROM terminarz_wizyty_doktorskie "
                    + " WHERE id_medyka=? AND data_start>=? AND data_stop<=?",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setInt(1, user.getID());
            stmt.setTimestamp(2, new java.sql.Timestamp(from.getTime()));
            stmt.setTimestamp(3, new java.sql.Timestamp(to.getTime()));
            return stmt.executeQuery();
        }
        catch(SQLException ex)
        {
            ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
        return null;
    }
    
    public  ResultSet getTodayVisit(String filter)
    {
        try{
            Calendar start = new GregorianCalendar();
            Calendar end = new GregorianCalendar();
            end.set(Calendar.HOUR_OF_DAY, 23);
            end.set(Calendar.MINUTE, 59);
            end.set(Calendar.SECOND, 59);
            start.set(Calendar.HOUR_OF_DAY, 0);
            start.set(Calendar.MINUTE, 0);
            start.set(Calendar.SECOND, 1);
            String queryStr = " SELECT T.id_pacjenta AS ID , T.data_start AS DATA , G.nazwa AS Gabinet ,U.imie AS IMIE , U.nazwisko AS NAZWISKO, " +
                    " P.pesel AS PESEL,P.nr_ubezpieczenia AS 'NUMER UBEZPIECZENIA' " +
                    " FROM terminarz_wizyty_doktorskie T JOIN users U ON T.id_pacjenta=U.id " 
                    + "JOIN pacjenci P ON T.id_pacjenta=P.id_user "
                    + "JOIN gabinety G ON T.id_gabinetu=G.id" +
                    " WHERE T.id_medyka=? AND T.data_start>=? AND T.data_stop<=?";
            if(!"".equals(filter))
            {
                queryStr += " AND (";
                queryStr += "U.imie LIKE '%";
                queryStr += filter;
                queryStr += "%' OR U.nazwisko LIKE '%";
                queryStr += filter;
                queryStr += "%'";
                queryStr += ")";
            }
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    queryStr,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setInt(1, user.getID());
            stmt.setTimestamp(2, new java.sql.Timestamp(start.getTimeInMillis()));
            stmt.setTimestamp(3, new java.sql.Timestamp(end.getTimeInMillis()));
            return stmt.executeQuery();
        }
        catch(SQLException ex)
        {
            ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
        return null;  
    }
    
    public ResultSet getHoliday(Date from,Date to)
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "SELECT data_start AS data_start,data_stop AS data_stop FROM medycy_zwolnienia WHERE id_medyka=? AND data_start BETWEEN ? AND ? AND data_stop BETWEEN ? AND ?",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            
            stmt.setInt(1, user.getID());
            stmt.setTimestamp(2, new java.sql.Timestamp(from.getTime()));
            stmt.setTimestamp(3, new java.sql.Timestamp(to.getTime()));
            stmt.setTimestamp(4, new java.sql.Timestamp(from.getTime()));
            stmt.setTimestamp(5, new java.sql.Timestamp(to.getTime()));
            return stmt.executeQuery();
        }
         catch(SQLException ex)
        {
            ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
        return null;
    }
    
        public ResultSet getHolidayById(Date from,Date to, int id)
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "SELECT data_start AS data_start,data_stop AS data_stop FROM medycy_zwolnienia WHERE id_medyka=? AND data_start BETWEEN ? AND ? AND data_stop BETWEEN ? AND ?",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            
            stmt.setInt(1, id);
            stmt.setTimestamp(2, new java.sql.Timestamp(from.getTime()));
            stmt.setTimestamp(3, new java.sql.Timestamp(to.getTime()));
            stmt.setTimestamp(4, new java.sql.Timestamp(from.getTime()));
            stmt.setTimestamp(5, new java.sql.Timestamp(to.getTime()));
            return stmt.executeQuery();
        }
         catch(SQLException ex)
        {
            ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
        return null;
    }
        
    public ResultSet getPatients(String filter) throws SQLException
    {
            Statement stmt = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        try{
            String queryStr = ""
                    + "SELECT P.id_user AS ID,U.imie AS IMIE , U.nazwisko AS NAZWISKO, "
                    + " P.pesel AS PESEL,P.adres AS ADRES,P.nr_ubezpieczenia AS 'NUMER UBEZPIECZENIA' , "
                    + " U.email AS 'E-MAIL' "
                    + " FROM pacjenci P JOIN users U"
                    + " ON P.id_user=U.id "
                    + " WHERE id_medyka = "+user.getID();
            if(!"".equals(filter))
            {
                String[] tmp = filter.split(" ");
                queryStr += " AND (";
                int i=0;
                for(String filtertmp : tmp){
                    if(i!=0) {
                        queryStr += " AND ";
                    }
                    queryStr += " U.imie LIKE '%";
                    queryStr += filtertmp;
                    queryStr += "%' OR U.nazwisko LIKE '%";
                    queryStr += filtertmp;
                    queryStr += "%' ";
                    i++;
                }
                queryStr += ")";
            }
            stmt.executeQuery(queryStr);
            return stmt.getResultSet();
        }
        catch(SQLException ex)
        {
            ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
        return null;
    }
    

    
    public boolean addDoctor(Doctor doctor, String password) throws SQLException{
         Statement stmt = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        try{
            stmt.execute("insert into users values("+doctor.user.getAuthorizationID()+",'"+doctor.user.getLogin()+"','"+
            password+"','"+doctor.user.getFirstName()+"','"+doctor.user.getLastName()+"','"+doctor.user.getEMail()+"',"+
            Integer.toString(1)+");");           
            
            stmt.execute("select id from users where login='"+doctor.user.getLogin()+"'");
            ResultSet rs = stmt.getResultSet();
            rs.next();
            int doc_id = rs.getInt(1);
            
            stmt.execute("select id from medycy_specjalizacje where nazwa = '"+doctor.specialization+"'");
            rs = stmt.getResultSet();
            rs.next();
            int spec_id = rs.getInt(1);
            
            stmt.execute("insert into medycy values ("+doc_id+","+spec_id+",'"+doctor.lic_numer+"')");
            
            return true;
        }
        catch(SQLException ex)
        {
            ErrorLogger.getInstance().log("Bład połączenia z bazą danych", true, user.getJFrameParent());
        }
        
        return false;
    }

    public static boolean deleteDoctor(String eMail){
    
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("select id from users where email='"+eMail+"'");
            ResultSet rs = stmn.getResultSet();
            rs.next();
            int id_user = rs.getInt(1);

            stmn.execute("delete from users where id="+id_user);
            
            return true;
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Błąd danych", true,parent);
        }       
        return false;
        
    }
    
    public static void addHistory(int idPatient,int idMedic,String data)
    {
        try{
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "INSERT INTO historia_wizyt VALUES(?,?,?,'','')");
            stmt.setInt(1, idPatient);
            stmt.setInt(2, idMedic);
            stmt.setString(3, data);
            stmt.executeUpdate();
        }
        catch(SQLException ex)
        {
             ErrorLogger.getInstance().log(ex.toString(), true,parent);
        }
    }
    
    public static String[] getAllSpecModel(){
                try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("select id, nazwa from medycy_specjalizacje");
            ResultSet rs = stmn.getResultSet();
            JTable tempTable = JTableUtil.createTable(rs);
            String[] model = new String[tempTable.getRowCount()];
            for(int i=0;i<tempTable.getRowCount();i++){
            model[i] = tempTable.getValueAt(i, 0).toString() + " " + tempTable.getValueAt(i, 1);
            }
            return model;
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Nie moge pobrać specjalizacji", true,parent);
        } 
        return null;
    }
    
    public static String[] getDoctorsModelBySpec(String spec){
                try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("select U.id, U.imie, U.nazwisko from medycy as M join users as U on "
                    + "U.id=M.id_user join medycy_specjalizacje as MS on M.id_specjalizacji=MS.id where MS.nazwa='"
                    +spec+"'");
            ResultSet rs = stmn.getResultSet();
            JTable tempTable = JTableUtil.createTable(rs);
            String[] model = new String[tempTable.getRowCount()];
            for(int i=0;i<tempTable.getRowCount();i++){
            model[i] = tempTable.getValueAt(i, 0).toString() + " " + tempTable.getValueAt(i, 1)+ " " + tempTable.getValueAt(i, 2);
            }
            return model;
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Nie moge pobrać modelu po specjalizacji", true,parent);
        }        
        
        return null;
    }
}
